MySQL 常见问题

请你描述下事务的特性？

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

一致性：执行事务前后，数据库从一个一致性状态转换到另一个一致性状态。

隔离性：并发访问数据库时，一个用户的事物不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库 发生故障也不应该对其有任何影响。

说一下 MySQL 的行锁和表锁？

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

InnoDB 的表锁是通过意向锁来实现的，意向锁是由数据弓|擎自己维护的，用户无法手动操作意向锁。一个事务成功地给一张表加上表锁的前提：没有其他任何事务已经锁定了这张表的任意一行数据，而如何知道没有事务占用了呢？这就是靠的意向锁

具体看下面

说一下脏读，不可重复读和幻读

脏读： 意味着一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。即这个事物读取的数据是不正确的

不可重复读： 在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。 这是由于查询时系统中其他事务修改的提交而引起的。即这个事物在读的过程中被 修改 了。（主要原因就是因为，读已提交，每次在读完数据后都释放这个共享锁）

幻读：当一个事物对整个 table 进行修改之后，第二个事物向表中 插入 了一行数据，此时第一个事物发现了新插入的没有修改的数据行，好像发生了幻觉一样

数据库的隔离级别

读未提交 RU

1. 一个事务还没提交时，它做的变更就能被别的事务看到
2. 会出现幻读，不可重复读，脏读
3. 更新数据时加上 行级共享锁，事物结束即释放

读已提交 RC

1. 一个事务提交之后，它做的变更才会被其他事务看到
2. 会出现幻读，不可重复读，不会出现脏读
3. 写数据加 行级排他锁，这样写过程是无法读取的，直到事务处理完毕才释放排他锁，给读的数据加 行级共享锁，这样读的时候也是无法写的，但是 一旦读完该行就释放共享锁
4. MySQL 会在 SQL 语句开始执行时创建一个视图

可重复读 RR

1. 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
2. 会出现幻读，不会出现不可重复读，脏读
3. 给写的数据加 行级排他锁，事务结束释放，给读的数据加行级共享锁，事务结束后释放
4. MySQL 会在事物开始时创建一个一致性视图（接下面的 MVCC），事物结束时销毁

可串行化 S

1. 当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
2. 不会出现幻读，不可重复读，脏读
3. 事务读数据则加 表级共享锁，事务写数据则加 表级排他锁
4. 不区分快照读与当前读

MVCC 的原理

当执行 select 操作时 innodb 默认会执行快照读，会记录下这次 select 后的结果，之后 select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前 select 的数据，这就实现了可重复读了。

快照的生成当在第一次执行 select 的时候，也就是说假设当 A 开启了事务，然后没有执行任何操作，这时候 B insert 了一条数据然后 commit，这时候 A 执行 select，那么返回的数据中就会有 B 添加的那条数据。

之后无论再有其他事务 commit 都没有关系，因为快照已经生成了，后面的 select 都是根据快照来的。

使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

• 版本未提交，不可见；
• 版本已提交，但是是在一致性视图创建后提交的，不可见；
• 版本已提交，而且是在一致性视图创建前提交的，可见

在 MVCC 中有两种读，上面三种是快照读，还有一种是当前读

1. 当普通的 select 是快照读
2. 插入，删除，更新属于当前读，需要加锁，遵从两阶段锁协议

说一下内连接和外连接

内连接也叫自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取两个表中所匹配的数据，舍弃不匹配的数据

select fieldlist from table1 [inner] join table2 on table1.column = table2.column

内连接保证两个表中的所有行都满足条件，而外连接则不然，外连接不仅仅包含符合连接条件的行，而且还包括左表（左外连接），右表（右外连接），或者两个边表（全外连接）中的所有数据行

select fieldlist from table1 left/ right outer join table2 on table1.column = table2.column

join 的 on 和 where 的区别

在使用 left join 时，on 和 where 条件的区别如下：

1、on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

所以：join on 后面的条件已经把结果过滤了一遍，而 where 则是把限制条件放到最后，执行最后一次查询前结果里值变多了，查询起来变慢了，效率自然变低了。

因此，有一个显而易见的 SQL 优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx 的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

MySQL 如何加锁

除了各个隔离级别 MySQL 自动的加锁，在某些情况下，用户需要 显式地对数据库读取操作进行加锁 以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于 SELECT 的只读操作。

InnoDB 存储引擎对于 SELECT 语句支持两种一致性的锁定读（locking read）操作：

• SELECT...FOR UPDATE ： 对读取的行记录加一个 X 锁
• SELECT...LOCK IN SHARE MODE ： 对读取的行记录加一 个 S 锁

select * from table where id = ? lock in share mode
select * from table where id < ? lock in share mode
select * from table where id = ? for update
select * from table where id < ? for update

SELECT...FOR UPDATE 对读取的行记录加一个 X 锁，其他事务不能对已锁定的行加上任何锁。 SELECT...LOCK IN SHARE MODE 对读取的行记录加一 个 S 锁，其他事务可以向被锁定的行加 S 锁，但是如果加 X 锁，则会被阻塞。

对于一致性非锁定读，即使读取的行已被执行了 SELECT...FOR UPDATE，也是可以进行读取的，这和之前讨论的情况一样。此外，SELECT...FOR UPDATE、SELECT...LOCK IN SHARE MODE 必须在一个事务中，当事务提交了，锁也就释放了。

因此在使用，上述两句 SELECT 锁定语句时，务必加上 BEGIN、START TRANSACTION 或者 SET AUTOCOMMIT=0

谈谈你对聚簇索引的理解？

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

聚簇索引和非聚簇索引的区别：

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

谈谈你对索引的理解？

索引优点：索引通过 B+ 树，把对某个字段的查询降到了 $O(logn)$ 级别，极大的提高了效率

索引缺点：创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

建立索引的原则

1. 只为⽤于搜索、排序或分组的列创建索引
2. 考虑列的基数 ，基数越大，创建索引的效果越好
3. 索引列的类型尽量⼩，这样B+树中每个页存储的数据就会更多
4. 写多读少尽量不要建立索引
5. 可以使用倒叙索引或者 hash 索引
6. InnoDB 的主键尽量用 MySQL 的自增主键

不适合建立索引的情况：

1. 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引；
2. 对于一些特殊的数据类型，不宜建立索引，比如：文本字段（text）等。

谈谈你对覆盖索引的认识？

如果一个索引包含了满足查询语句中字段与条件的数据就叫做覆盖索引。具有以下优点：

1. 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
2. 一些存储引擎（例如：MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

谈谈你对 B+ 树的理解？

1、B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。

2、在 B+ 树中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1。

3、进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

4、插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

谈谈你对哈希索引的理解？

哈希索引能以 O(1) 时间进行查找，但是失去了有序性。无法用于排序与分组、只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫 “自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+ 树索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如：快速的哈希查找。

谈谈你对最左前缀原则的理解？

1、最左前缀匹配原则会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如：a = 1 and b = 2 and c > 3 and d = 4 如果建立 (a, b, c, d) 顺序的索引，d 是用不到索引的。如果建立 (a, b, d, c) 的索引则都可以用到，a、b、d 的顺序可以任意调整。

2、= 和 in 可以乱序，比如：a = 1 and b = 2 and c = 3 建立 (a, b ,c) 索引可以任意顺序，MySQL 的优化器会优化成索引可以识别的形式。

什么情况下索引会失效？即查询不走索引？

1、索引列参与表达式计算：

SELECT 'sname' FROM 'stu' WHERE 'age' + 10 = 30;

2、 函数运算：

SELECT 'sname' FROM 'stu' WHERE LEFT('date',4) < 1990; 

3、%词语%–模糊查询：

SELECT * FROM 'manong' WHERE `uname` LIKE '码农%' -- 走索引 

SELECT * FROM 'manong' WHERE `uname` LIKE '%码农%' -- 不走索引 

4、 字符串与数字比较不走索引：

5、查询条件中有 or ，即使其中有条件带索引也不会使用。

select * from dept where dname='xxx' or loc='xx' or deptno = 45;

6、正则表达式不使用索引。

7、MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引。

主从复制中涉及到哪三个线程？

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。

I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。

SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。

行锁的算法（种类）

• Record Lock：单行记录
• Gap Lock：间隙锁，锁定一个范围，但不包含锁定记录
• Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身， MySql 防止幻读，就是使用此锁实现